/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package src.com.dados.midiateca;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import src.com.negocio.midiateca.EmprestimoMaterial;
import src.com.dados.Conexao;
import src.com.negocio.midiateca.MaterialDidatico;

/**
 *
 * @author Adriano
 */
public class EmprestimoMaterialDAO {

    MaterialDidatico material = new MaterialDidatico();

    public boolean emprestarMaterial(EmprestimoMaterial novoEmprestimo) {
        Connection con;

        con = Conexao.getInstance().getCon();
        String sql = "INSERT INTO emprestimo (cod_material, aluno_matricula, data_inicio, data_fim, devolvido) values (?,?,?,?,?)";
        PreparedStatement pstmt;

        try {
            // cria um preparedStatement
            pstmt = con.prepareStatement(sql);

            // preenche os valores
            pstmt.setInt(1, novoEmprestimo.getCodMaterial());
            pstmt.setInt(2, novoEmprestimo.getAlunoMatricula());
            pstmt.setDate(3, novoEmprestimo.getDataInicio());
            pstmt.setDate(4, novoEmprestimo.getDataFim());
            pstmt.setBoolean(5, novoEmprestimo.isDevolvido());

            pstmt.executeUpdate();

            System.out.println("Emprestimo realizado!");
            return true;
        } catch (SQLException e) {
        }
        return false;
    }

    public EmprestimoMaterial buscarEmprestimoMaterial(int matriculaAluno, int codigoMaterial) throws SQLException {
        Connection con;

        con = Conexao.getInstance().getCon();
        String sql = "SELECT * FROM emprestimo WHERE aluno_matricula = " + matriculaAluno + " AND cod_material = " + codigoMaterial;

        try {
            Statement stm;
            stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sql);

            if (rs.next()) {
                EmprestimoMaterial emprestimoEncontrado = new EmprestimoMaterial();
                emprestimoEncontrado.setId(rs.getInt("id"));
                emprestimoEncontrado.setCodMaterial(rs.getInt("cod_material"));
                emprestimoEncontrado.setAlunoMatricula(rs.getInt("aluno_matricula"));
                emprestimoEncontrado.setDataInicio(rs.getDate("data_inicio"));
                emprestimoEncontrado.setDataFim(rs.getDate("data_fim"));
                emprestimoEncontrado.setDevolvido(rs.getBoolean("devolvido"));

                return emprestimoEncontrado;
            }
        } catch (SQLException e) {
            System.out.println(e);
        }
        return null;
    }

    public boolean devolverMaterial(EmprestimoMaterial emprestimoMaterial) throws SQLException {
        Connection con;
        con = Conexao.getInstance().getCon();
        Statement stmt = con.createStatement();
        Date dataAtual = new Date(System.currentTimeMillis()); 
        System.out.println(dataAtual);
        //String sql = "UPDATE emprestimo SET devolvido = 1, data_devolucao = " + dataAtual + " WHERE aluno_matricula = " + matriculaAluno + " AND cod_material = " + codigoMaterial;

        if (emprestimoMaterial != null) {
            String sql = "UPDATE emprestimo SET devolvido = 1 , data_devolucao = '" + dataAtual +"' WHERE aluno_matricula = " + emprestimoMaterial.getAlunoMatricula() + " AND cod_material = " + emprestimoMaterial.getCodMaterial();
            stmt.executeUpdate(sql);
            return true;
        }
        return false;
    }
    
    public ArrayList<String> consultaMaterial() throws SQLException {
        Connection conexao;
        ArrayList<String> result = new ArrayList<String>();
        String select_unidade = "SELECT tipo_mat FROM material;";
        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        //Class.forName("com.mysql.jdbc.Driver");
        conexao = DriverManager.getConnection(url, "root", "dados");
        //System.out.println("2 - " + select_ano);

        PreparedStatement stmt = conexao.prepareStatement(select_unidade);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            result.add(rs.getString(1));
            // System.out.println(rs.getString(1));
        }

        conexao.close();
        return result;    
    }
    
    public String id_material(String mat) throws SQLException {
        Connection conexao;
        String result = null;
       
        String select_codMaterial = "SELECT codigo_mat FROM material where tipo_mat like '"+mat+"';";
        

        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");
        PreparedStatement stmt = conexao.prepareStatement(select_codMaterial);
        ResultSet rs = stmt.executeQuery();



        while (rs.next()) {
            result = rs.getString(1);
        }

        conexao.close();
        return result;


    }
}
